Imports System.Data.OleDb

Public Class ExcelHandling

    Private _SheetName As String
    Private _FormatID As Integer
    '    Private _FilePath As String

    Public Property SheetName() As String
        Get
            Return _SheetName
        End Get
        Set(ByVal value As String)
            _SheetName = value
        End Set
    End Property
    Public Property FormatID() As Integer
        Get
            Return _FormatID
        End Get
        Set(ByVal value As Integer)
            _FormatID = value
        End Set
    End Property
    Public Property FilePath() As String
        Get
            Return _SheetName
        End Get
        Set(ByVal value As String)
            _SheetName = value
        End Set
    End Property

    Public Sub GetDataFromExcel(ByVal isHeaderNeeded As Boolean, ByVal filepath As String, ByVal sheetName As String, ByRef excelRecords As DataSet)

        Dim Query As String = ""
        Dim objdaCP As OleDbDataAdapter = Nothing
        Dim objConn As OleDbConnection = Nothing
        Try
            objConn = GetOleDbConnection(filepath, sheetName)
            objConn.Open()
            If (isHeaderNeeded) Then
                Query = "Select Top 1 * from [" & sheetName & "$]"
            Else
                Query = "Select * from [" & sheetName & "$]"
            End If
            objdaCP = New OleDbDataAdapter(Query, objConn)
            objdaCP.Fill(excelRecords, "CP_Investor")
        Catch ex As Exception
        Finally
            If objConn.State = ConnectionState.Open Then
                objConn.Close()
            End If
            objConn.Dispose()
            objdaCP.Dispose()
            objConn = Nothing
            objdaCP = Nothing
            Query = Nothing
        End Try


    End Sub

    ''' <summary>
    ''' Checks The Duplicate Columns
    ''' </summary>
    ''' <param name="dtInvestor"></param>
    ''' <param name="selectSheet"></param>
    ''' <param name="isColumDupliacte"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function CheckDuplicateColumn(ByRef colName As String, ByVal filename As String, ByRef dtInvestor As DataTable, ByVal selectSheet As String, ByRef isColumDupliacte As Boolean) As Boolean
        Dim ExcWorksheet As Excel.Worksheet = Nothing
        Dim ExcelRange As Excel.Range
        Dim ExcelWorkBook As Excel.Application = Nothing
        Dim ExcelColumn As New ArrayList()
        Dim CellCount As Integer
        Dim CurrentCount As Integer = 1
        'Try
        ExcelWorkBook = New Excel.Application
        ExcelWorkBook.Workbooks.Open(filename)
        ' selectSheet = _SheetName 'cmbSheets.Text

        CellCount = CType(ExcelWorkBook.Sheets(selectSheet), Excel.Worksheet).Cells.Count
        CurrentCount = 1
        ExcWorksheet = ExcelWorkBook.Sheets(selectSheet)

        While CurrentCount < CellCount
            ExcelRange = ExcWorksheet.Cells(1, CurrentCount)
            If ExcelRange.Text = "" Then Exit While
            If ExcelColumn.IndexOf(ExcelRange.Text.ToString.ToUpper) > 0 Then
                colName = ExcelRange.Text.ToString.Trim
                ' MessageBox.Show("Column '" + ExcelRange.Text & "' is duplicate in file", mstrMsgBoxCaption, MessageBoxButtons.OK, MessageBoxIcon.Information)
                ExcelColumn = Nothing
                isColumDupliacte = True
                ExcelWorkBook.Workbooks.Close()
                ExcelWorkBook.Quit()
                Return False
                Exit Function
            End If
            ExcelColumn.Add(ExcelRange.Text.ToString.ToUpper)
            dtInvestor.Columns(CurrentCount - 1).ColumnName = ExcelRange.Text
            CurrentCount += 1
        End While
        ExcelWorkBook.Workbooks.Close()
        ExcelWorkBook.Quit()
        Return True
        'Catch ex As Exception
        '    If (Not ExcelWorkBook Is Nothing) Then
        '        ExcelWorkBook.Workbooks.Close()
        '        ExcelWorkBook.Quit()
        '    End If

        '    Throw ex
        'Finally
        '    ExcWorksheet = Nothing
        '    ExcelRange = Nothing
        '    ExcelWorkBook = Nothing
        '    ExcelColumn = Nothing
        '    selectSheet = Nothing
        'End Try
    End Function

    ' ''' <summary>
    ' ''' 'To Select Sheet Names of the selected Investor Excel file
    ' ''' </summary>
    ' ''' <param name="File"></param>
    ' ''' <returns></returns>
    ' ''' <remarks></remarks>
    'Public Function GetWorksheetNames(ByVal File As String) As DataTable
    '    ' Dim ReturnVal As Boolean = False
    '    Dim XLApp As New Excel.Application
    '    Dim ExcWorkbook As Excel.Workbook
    '    Dim SheetCnt As Integer
    '    Dim WorkBooks As New DataTable
    '    Dim WorkBook As DataRow
    '    ExcWorkbook = XLApp.Workbooks.Open(File)
    '    '        Dim SheetNames(ExcWorkbook.Worksheets.Count) As String

    '    'Try

    '    WorkBooks.Columns.Add("SNo.", GetType(Integer))
    '    WorkBooks.Columns.Add("Name", GetType(String))
    '    WorkBook = WorkBooks.NewRow
    '    WorkBook.Item("SNo.") = -1
    '    WorkBook.Item("Name") = "---Select---"
    '    WorkBooks.Rows.InsertAt(WorkBook, 0)
    '    If ExcWorkbook.Worksheets.Count > 0 Then
    '        For SheetCnt = 0 To ExcWorkbook.Worksheets.Count - 1
    '            WorkBook = WorkBooks.NewRow
    '            WorkBook.Item("SNo.") = SheetCnt + 1
    '            WorkBook.Item("Name") = ExcWorkbook.Worksheets(SheetCnt + 1).Name
    '            WorkBooks.Rows.Add(WorkBook)
    '        Next
    '    End If
    '    ExcWorkbook.Close()
    '    ExcWorkbook = Nothing
    '    XLApp.Workbooks.Close()

    '    XLApp.Quit()
    '    System.Runtime.InteropServices.Marshal.ReleaseComObject(XLApp)
    '    XLApp = Nothing
    '    Return WorkBooks

    '    'Catch ex As Exception
    '    '    Return Nothing
    '    'Finally
    '    '    XLApp = Nothing
    '    '    ExcWorkbook = Nothing
    '    '    WorkBooks = Nothing
    '    '    WorkBook = Nothing
    '    '    SheetNames = Nothing
    '    '    'WorkBook = Nothing
    '    'End Try
    'End Function

    ''' <summary>
    ''' Gets The oleDbConnection
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Protected Function GetOleDbConnection(ByVal fullFilename As String, ByVal sheetName As String) As OleDbConnection
        Dim ConnectionStringAccess As String = ""
        Dim objConn As OleDbConnection = Nothing
        'Try
        ConnectionStringAccess = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fullFilename + ";Extended Properties=""Excel 8.0;IMEX=1"""
        objConn = New OleDbConnection(ConnectionStringAccess)
        Return objConn

        'Catch ex As Exception
        '    Throw ex
        'Finally
        '    fullFilename = Nothing
        '    ConnectionStringAccess = Nothing
        '    objConn = Nothing
        'End Try
    End Function

End Class
